<!DOCTYPE html>
<html lang="zh-CN">
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width,initial-scale=1">
    <title>mysql-udf安装 | lilei</title>
    <meta name="generator" content="VuePress 1.8.2">
    <link rel="icon" href="/images/icons/logo.png">
    <link rel="apple-touch-icon" href="/images/icons/logo.png">
    <link rel="manifest" href="/manifest.json">
    <script type="text/javascript" src="/assets/js/load.js"></script>
    <meta name="description" content="个人网站">
    <meta name="twitter:title" content="mysql-udf安装">
    <meta name="twitter:description" content="">
    <meta name="twitter:card" content="summary">
    <meta name="twitter:url" content="http://www.lileiblog.xyz/SQL/mysql-udf%E5%AE%89%E8%A3%85.html">
    <meta property="og:type" content="article">
    <meta property="og:title" content="mysql-udf安装">
    <meta property="og:description" content="">
    <meta property="og:url" content="http://www.lileiblog.xyz/SQL/mysql-udf%E5%AE%89%E8%A3%85.html">
    <meta property="og:site_name" content="lilei">
    <meta itemprop="name" content="mysql-udf安装">
    <meta itemprop="description" content="">
    <meta name="theme-color" content="#3eaf7c">
    <meta name="apple-mobile-web-app-capable" content="yes">
    <meta name="apple-mobile-web-app-status-bar-style" content="black">
    <meta name="msapplication-TileImage" content="/images/icons/logo.png">
    <meta name="msapplication-TileColor" content="#ffffff">
    <meta name="keywords" content="lilei,vue,vuepress,vuepress-theme,theme,主题,vuepress主题,blog
        ,vuepress-blog,java,python,shell,sql,golang,script,shell script,nginx,windows,去广告,androd,google drive
        ,one drive,idea,eclipse,git,小程序">
    <meta name="referrer" content="never">
    
    <link rel="preload" href="/assets/css/0.styles.8932179b.css" as="style"><link rel="preload" href="/assets/js/app.269009d6.js" as="script"><link rel="preload" href="/assets/js/3.974b9720.js" as="script"><link rel="preload" href="/assets/js/55.01c4b876.js" as="script"><link rel="prefetch" href="/assets/js/10.5915e99b.js"><link rel="prefetch" href="/assets/js/11.ff732762.js"><link rel="prefetch" href="/assets/js/12.8bb86d91.js"><link rel="prefetch" href="/assets/js/13.18bde5b0.js"><link rel="prefetch" href="/assets/js/14.314fae34.js"><link rel="prefetch" href="/assets/js/15.0a4ff48f.js"><link rel="prefetch" href="/assets/js/16.ffaf1503.js"><link rel="prefetch" href="/assets/js/17.49a31a6e.js"><link rel="prefetch" href="/assets/js/18.7e6443bf.js"><link rel="prefetch" href="/assets/js/19.6880cc9a.js"><link rel="prefetch" href="/assets/js/20.1fb3f255.js"><link rel="prefetch" href="/assets/js/21.0cd39eea.js"><link rel="prefetch" href="/assets/js/22.392d85cb.js"><link rel="prefetch" href="/assets/js/23.f50adb15.js"><link rel="prefetch" href="/assets/js/24.43e324e1.js"><link rel="prefetch" href="/assets/js/25.e3663205.js"><link rel="prefetch" href="/assets/js/26.da141fb8.js"><link rel="prefetch" href="/assets/js/27.6c05235a.js"><link rel="prefetch" href="/assets/js/28.c124245a.js"><link rel="prefetch" href="/assets/js/29.d185870d.js"><link rel="prefetch" href="/assets/js/30.8587d422.js"><link rel="prefetch" href="/assets/js/31.dc382b56.js"><link rel="prefetch" href="/assets/js/32.20ba32e9.js"><link rel="prefetch" href="/assets/js/33.6ecd7de9.js"><link rel="prefetch" href="/assets/js/34.2b91c13a.js"><link rel="prefetch" href="/assets/js/35.66ed0733.js"><link rel="prefetch" href="/assets/js/36.ce7bca84.js"><link rel="prefetch" href="/assets/js/37.d00bb5ac.js"><link rel="prefetch" href="/assets/js/38.c3ecbb6f.js"><link rel="prefetch" href="/assets/js/39.308f4175.js"><link rel="prefetch" href="/assets/js/4.520f6ecc.js"><link rel="prefetch" href="/assets/js/40.f4b98bc2.js"><link rel="prefetch" href="/assets/js/41.95fae64f.js"><link rel="prefetch" href="/assets/js/42.fe4f877b.js"><link rel="prefetch" href="/assets/js/43.ca8f222e.js"><link rel="prefetch" href="/assets/js/44.4d4ee79e.js"><link rel="prefetch" href="/assets/js/45.79178b33.js"><link rel="prefetch" href="/assets/js/46.c3817d0b.js"><link rel="prefetch" href="/assets/js/47.b8252443.js"><link rel="prefetch" href="/assets/js/48.df9d5d3b.js"><link rel="prefetch" href="/assets/js/49.d812fdb2.js"><link rel="prefetch" href="/assets/js/5.f1a24a16.js"><link rel="prefetch" href="/assets/js/50.55cfcaa8.js"><link rel="prefetch" href="/assets/js/51.b8608b02.js"><link rel="prefetch" href="/assets/js/52.52c5e00d.js"><link rel="prefetch" href="/assets/js/53.3a9bd5fb.js"><link rel="prefetch" href="/assets/js/54.a4240c13.js"><link rel="prefetch" href="/assets/js/56.f29549a3.js"><link rel="prefetch" href="/assets/js/57.01438a04.js"><link rel="prefetch" href="/assets/js/58.29bb4626.js"><link rel="prefetch" href="/assets/js/59.e8b89408.js"><link rel="prefetch" href="/assets/js/6.8c478b39.js"><link rel="prefetch" href="/assets/js/60.bedb5381.js"><link rel="prefetch" href="/assets/js/61.bdfbe584.js"><link rel="prefetch" href="/assets/js/62.cb825d1a.js"><link rel="prefetch" href="/assets/js/63.a9516572.js"><link rel="prefetch" href="/assets/js/64.aa854ed7.js"><link rel="prefetch" href="/assets/js/65.285f883d.js"><link rel="prefetch" href="/assets/js/66.12849f37.js"><link rel="prefetch" href="/assets/js/67.c18b00e4.js"><link rel="prefetch" href="/assets/js/68.fa59daa9.js"><link rel="prefetch" href="/assets/js/69.e9d5dda4.js"><link rel="prefetch" href="/assets/js/7.cfb0d0ea.js"><link rel="prefetch" href="/assets/js/70.db126cce.js"><link rel="prefetch" href="/assets/js/71.6acb04cd.js"><link rel="prefetch" href="/assets/js/72.7ff0ab11.js"><link rel="prefetch" href="/assets/js/73.9a66da79.js"><link rel="prefetch" href="/assets/js/74.0a38a030.js"><link rel="prefetch" href="/assets/js/75.7b32dbb3.js"><link rel="prefetch" href="/assets/js/8.cb9c08e5.js"><link rel="prefetch" href="/assets/js/9.ad652cfe.js"><link rel="prefetch" href="/assets/js/vendors~flowchart.110a1065.js">
    <link rel="stylesheet" href="/assets/css/0.styles.8932179b.css">
  </head>
  <body>
    <div id="app" data-server-rendered="true"><div class="theme-container"><header class="navbar"><div class="sidebar-button"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" role="img" viewBox="0 0 448 512" class="icon"><path fill="currentColor" d="M436 124H12c-6.627 0-12-5.373-12-12V80c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12z"></path></svg></div> <a href="/" class="home-link router-link-active"><img src="/images/icons/logo.png" alt="lilei" class="logo"> <span class="site-name can-hide">lilei</span></a> <div class="links"><div class="search-box"><input aria-label="Search" autocomplete="off" spellcheck="false" value=""> <!----></div> <nav class="nav-links can-hide"><div class="nav-item"><a href="/Form/" class="nav-link">
  Form
</a></div><div class="nav-item"><a href="/IDE/" class="nav-link">
  IDE
</a></div><div class="nav-item"><a href="/Java/" class="nav-link">
  Java
</a></div><div class="nav-item"><a href="/Other/" class="nav-link">
  Other
</a></div><div class="nav-item"><a href="/Python/" class="nav-link">
  Python
</a></div><div class="nav-item"><a href="/SQL/" class="nav-link router-link-active">
  SQL
</a></div><div class="nav-item"><a href="/Shell/" class="nav-link">
  Shell
</a></div><div class="nav-item"><a href="/System/" class="nav-link">
  System
</a></div> <a href="https://github.com/woytu/notes-vuepress" target="_blank" rel="noopener noreferrer" class="repo-link">
    GitHub
    <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></nav></div></header> <div class="sidebar-mask"></div> <aside class="sidebar"><nav class="nav-links"><div class="nav-item"><a href="/Form/" class="nav-link">
  Form
</a></div><div class="nav-item"><a href="/IDE/" class="nav-link">
  IDE
</a></div><div class="nav-item"><a href="/Java/" class="nav-link">
  Java
</a></div><div class="nav-item"><a href="/Other/" class="nav-link">
  Other
</a></div><div class="nav-item"><a href="/Python/" class="nav-link">
  Python
</a></div><div class="nav-item"><a href="/SQL/" class="nav-link router-link-active">
  SQL
</a></div><div class="nav-item"><a href="/Shell/" class="nav-link">
  Shell
</a></div><div class="nav-item"><a href="/System/" class="nav-link">
  System
</a></div> <a href="https://github.com/woytu/notes-vuepress" target="_blank" rel="noopener noreferrer" class="repo-link">
    GitHub
    <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></nav>  <ul class="sidebar-links"><li><section class="sidebar-group depth-0"><p class="sidebar-heading open"><span>SQL</span> <!----></p> <ul class="sidebar-links sidebar-group-items"><li><a href="/SQL/" aria-current="page" class="sidebar-link">SQL</a></li><li><a href="/SQL/MySQL事件.html" class="sidebar-link">MySQL事件</a></li><li><a href="/SQL/MySQL备份恢复数据.html" class="sidebar-link">MySQL备份恢复数据</a></li><li><a href="/SQL/MySQL存储过程.html" class="sidebar-link">MySQL存储过程</a></li><li><a href="/SQL/MySQL安装配置.html" class="sidebar-link">MySQL安装配置</a></li><li><a href="/SQL/MySQL常见问题.html" class="sidebar-link">MySQL常见问题</a></li><li><a href="/SQL/MySQL笔记.html" class="sidebar-link">MySQL笔记</a></li><li><a href="/SQL/mysql-udf安装.html" class="active sidebar-link">mysql-udf安装</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header"><a href="/SQL/mysql-udf安装.html#一、mysql-udf-http" class="sidebar-link">一、mysql-udf-http</a></li><li class="sidebar-sub-header"><a href="/SQL/mysql-udf安装.html#二、mysql-udf-json" class="sidebar-link">二、mysql-udf-json</a></li></ul></li></ul></section></li></ul> </aside> <main class="page"> <div class="theme-default-content content__default"><h1 id="mysql-udf安装"><a href="#mysql-udf安装" class="header-anchor">#</a> mysql-udf安装</h1> <p></p><div class="table-of-contents"><ul><li><a href="#一、mysql-udf-http">一、mysql-udf-http</a><ul><li><a href="#安装cur">安装cur</a></li><li><a href="#安装mysql-udf-http">安装mysql-udf-http</a></li><li><a href="#创建">创建</a></li><li><a href="#删除">删除</a></li><li><a href="#验证是否安装成功">验证是否安装成功</a></li><li><a href="#description">Description</a></li><li><a href="#实例">实例</a></li></ul></li><li><a href="#二、mysql-udf-json">二、mysql-udf-json</a><ul><li><a href="#创建">创建</a></li><li><a href="#删除">删除</a></li><li><a href="#验证是否安装成功">验证是否安装成功</a></li><li><a href="#实例">实例</a></li><li><a href="#创建触发器">创建触发器</a></li></ul></li></ul></div><p></p> <h2 id="一、mysql-udf-http"><a href="#一、mysql-udf-http" class="header-anchor">#</a> 一、mysql-udf-http</h2> <h3 id="安装cur"><a href="#安装cur" class="header-anchor">#</a> 安装cur</h3> <div class="language-sql extra-class"><pre class="language-sql"><code>yum install <span class="token operator">-</span>y libcurl<span class="token operator">*</span>
</code></pre></div><ul><li>或者</li></ul> <div class="language-sql extra-class"><pre class="language-sql"><code>yum install <span class="token operator">-</span>y curl<span class="token operator">*</span>
</code></pre></div><h3 id="安装mysql-udf-http"><a href="#安装mysql-udf-http" class="header-anchor">#</a> 安装mysql-udf-http</h3> <blockquote><p>项目网址：http://code.google.com/p/mysql-udf-http/</p> <p>中文说明：http://blog.zyan.cc/mysql-udf-http/</p></blockquote> <div class="language-sql extra-class"><pre class="language-sql"><code>tar zxvf mysql<span class="token operator">-</span>udf<span class="token operator">-</span>http<span class="token operator">-</span><span class="token number">1.0</span><span class="token punctuation">.</span>tar<span class="token punctuation">.</span>gz
cd mysql<span class="token operator">-</span>udf<span class="token operator">-</span>http<span class="token operator">-</span><span class="token number">1.0</span>
<span class="token punctuation">.</span><span class="token operator">/</span>configure <span class="token comment">--prefix=/usr/local/mysql-udf-http --with-mysql=/usr/local/mysql/bin/mysql_config</span>
make <span class="token operator">&amp;&amp;</span> make install
</code></pre></div><ul><li>如果提示<code>configure: error: no acceptable C compiler found in $PATH</code>需要安装<code>GCC</code>编译器</li></ul> <div class="language-sql extra-class"><pre class="language-sql"><code>yum install <span class="token operator">-</span>y gcc
</code></pre></div><ul><li>如果没有<code>mysql_config</code>请执行以下命令</li></ul> <div class="language-sql extra-class"><pre class="language-sql"><code>yum install <span class="token operator">-</span>y mysql<span class="token operator">-</span>devel
</code></pre></div><ul><li>搜索<code>mysql-udf-http.so</code>文件位置</li></ul> <div class="language-sql extra-class"><pre class="language-sql"><code>find <span class="token operator">/</span> <span class="token operator">-</span>name mysql<span class="token operator">-</span>udf<span class="token operator">-</span>http<span class="token punctuation">.</span>so
</code></pre></div><blockquote><p>由于<code>mysql-udf-http.so</code>不在<code>mysql/lib/plugin/</code>目录下，所以需要创建软连接或者复制过去</p></blockquote> <ul><li>创建软连接</li></ul> <div class="language-sql extra-class"><pre class="language-sql"><code>ln <span class="token operator">-</span>s <span class="token operator">/</span>usr<span class="token operator">/</span><span class="token keyword">local</span><span class="token operator">/</span>mysql<span class="token operator">-</span>udf<span class="token operator">-</span>http<span class="token operator">/</span>lib<span class="token operator">/</span>mysql<span class="token operator">-</span>udf<span class="token operator">-</span>http<span class="token punctuation">.</span>so <span class="token operator">/</span>usr<span class="token operator">/</span><span class="token keyword">local</span><span class="token operator">/</span>mysql<span class="token operator">/</span>lib<span class="token operator">/</span>plugin<span class="token operator">/</span>mysql<span class="token operator">-</span>udf<span class="token operator">-</span>http<span class="token punctuation">.</span>so
</code></pre></div><ul><li>或者复制</li></ul> <div class="language-sql extra-class"><pre class="language-sql"><code>cp <span class="token operator">/</span>usr<span class="token operator">/</span><span class="token keyword">local</span><span class="token operator">/</span>mysql<span class="token operator">-</span>udf<span class="token operator">-</span>http<span class="token operator">/</span>lib<span class="token operator">/</span>mysql<span class="token operator">-</span>udf<span class="token operator">*</span> <span class="token operator">/</span>usr<span class="token operator">/</span><span class="token keyword">local</span><span class="token operator">/</span>mysql<span class="token operator">/</span>lib<span class="token operator">/</span>plugin<span class="token operator">/</span>
</code></pre></div><ul><li>重启mysql</li></ul> <div class="language-sql extra-class"><pre class="language-sql"><code>service mysqld restart
</code></pre></div><ul><li>或者</li></ul> <div class="language-sql extra-class"><pre class="language-sql"><code>systemctl restart mysqld<span class="token punctuation">.</span>service
</code></pre></div><h3 id="创建"><a href="#创建" class="header-anchor">#</a> 创建</h3> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">create</span> <span class="token keyword">function</span> http_get <span class="token keyword">returns</span> string <span class="token keyword">soname</span> <span class="token string">'mysql-udf-http.so'</span><span class="token punctuation">;</span>
<span class="token keyword">create</span> <span class="token keyword">function</span> http_post <span class="token keyword">returns</span> string <span class="token keyword">soname</span> <span class="token string">'mysql-udf-http.so'</span><span class="token punctuation">;</span>
<span class="token keyword">create</span> <span class="token keyword">function</span> http_put <span class="token keyword">returns</span> string <span class="token keyword">soname</span> <span class="token string">'mysql-udf-http.so'</span><span class="token punctuation">;</span>
<span class="token keyword">create</span> <span class="token keyword">function</span> http_delete <span class="token keyword">returns</span> string <span class="token keyword">soname</span> <span class="token string">'mysql-udf-http.so'</span><span class="token punctuation">;</span>
</code></pre></div><h3 id="删除"><a href="#删除" class="header-anchor">#</a> 删除</h3> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">DROP</span> <span class="token keyword">FUNCTION</span> <span class="token keyword">IF</span> <span class="token keyword">EXISTS</span> http_get<span class="token punctuation">;</span>
<span class="token keyword">DROP</span> <span class="token keyword">FUNCTION</span> <span class="token keyword">IF</span> <span class="token keyword">EXISTS</span> http_post<span class="token punctuation">;</span>
<span class="token keyword">DROP</span> <span class="token keyword">FUNCTION</span> <span class="token keyword">IF</span> <span class="token keyword">EXISTS</span> http_put<span class="token punctuation">;</span>
<span class="token keyword">DROP</span> <span class="token keyword">FUNCTION</span> <span class="token keyword">IF</span> <span class="token keyword">EXISTS</span> http_delete<span class="token punctuation">;</span>
</code></pre></div><h3 id="验证是否安装成功"><a href="#验证是否安装成功" class="header-anchor">#</a> 验证是否安装成功</h3> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> mysql<span class="token punctuation">.</span>func<span class="token punctuation">;</span> 
</code></pre></div><h3 id="description"><a href="#description" class="header-anchor">#</a> Description</h3> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">SELECT</span> http_get<span class="token punctuation">(</span><span class="token string">'&lt;url&gt;'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> http_post<span class="token punctuation">(</span><span class="token string">'&lt;url&gt;'</span><span class="token punctuation">,</span> <span class="token string">'&lt;data&gt;'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> http_put<span class="token punctuation">(</span><span class="token string">'&lt;url&gt;'</span><span class="token punctuation">,</span> <span class="token string">'&lt;data&gt;'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> http_delete<span class="token punctuation">(</span><span class="token string">'&lt;url&gt;'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre></div><h3 id="实例"><a href="#实例" class="header-anchor">#</a> 实例</h3> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token comment">/* HTTP GET、POST方式提交关键词“xoyo”到百度移动搜索 */</span>  
<span class="token keyword">SELECT</span> http_get<span class="token punctuation">(</span><span class="token string">'http://m.baidu.com/s?word=xoyo&amp;pn=0'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>  
<span class="token keyword">SELECT</span> http_post<span class="token punctuation">(</span><span class="token string">'http://m.baidu.com/s'</span><span class="token punctuation">,</span><span class="token string">'word=xoyo&amp;pn=0'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>  
  
<span class="token comment">/* 新浪微博开放平台：获取新浪用户ID为103500的最近一条微博内容 */</span>  
<span class="token keyword">SELECT</span> http_get<span class="token punctuation">(</span><span class="token string">'http://api.t.sina.com.cn/statuses/user_timeline/103500.json?count=1&amp;source=1561596835'</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> <span class="token keyword">data</span><span class="token punctuation">;</span>  
<span class="token comment">/* 新浪微博开放平台：发表一条微博 */</span>  
<span class="token keyword">SELECT</span> http_post<span class="token punctuation">(</span><span class="token string">'http://your_sina_uid:your_password@api.t.sina.com.cn/statuses/update.xml?source=1561596835'</span><span class="token punctuation">,</span> <span class="token string">'status=Thins is sina weibo test information'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>  
  
<span class="token comment">/* Tokyo Tyrant 写入、读取、删除操作 */</span>  
<span class="token keyword">SELECT</span> http_put<span class="token punctuation">(</span><span class="token string">'http://192.168.8.34:1978/key'</span><span class="token punctuation">,</span> <span class="token string">'This is value'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>  
<span class="token keyword">SELECT</span> http_get<span class="token punctuation">(</span><span class="token string">'http://192.168.8.34:1978/key'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>  
<span class="token keyword">SELECT</span> http_delete<span class="token punctuation">(</span><span class="token string">'http://192.168.8.34:1978/key'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>  
</code></pre></div><h2 id="二、mysql-udf-json"><a href="#二、mysql-udf-json" class="header-anchor">#</a> 二、mysql-udf-json</h2> <div class="language-sql extra-class"><pre class="language-sql"><code>wget https:<span class="token comment">//github.com/claer-ding/lib_mysqludf_json/archive/2013.zip</span>
unzip <span class="token number">2013.</span>zip
cd lib_mysqludf_json<span class="token operator">-</span><span class="token number">2013</span>
gcc $<span class="token punctuation">(</span><span class="token operator">/</span>usr<span class="token operator">/</span><span class="token keyword">local</span><span class="token operator">/</span>mysql<span class="token operator">/</span>bin<span class="token operator">/</span>mysql_config <span class="token comment">--cflags) -shared -fPIC -o lib_mysqludf_json.so lib_mysqludf_json.c</span>
cp lib_mysqludf_json<span class="token punctuation">.</span>so <span class="token operator">/</span>usr<span class="token operator">/</span><span class="token keyword">local</span><span class="token operator">/</span>mysql<span class="token operator">/</span>lib<span class="token operator">/</span>plugin<span class="token operator">/</span>
</code></pre></div><h3 id="创建-2"><a href="#创建-2" class="header-anchor">#</a> 创建</h3> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">create</span> <span class="token keyword">function</span> lib_mysqludf_json_info <span class="token keyword">returns</span> string <span class="token keyword">soname</span> <span class="token string">'lib_mysqludf_json.so'</span><span class="token punctuation">;</span>
<span class="token keyword">create</span> <span class="token keyword">function</span> json_array <span class="token keyword">returns</span> string <span class="token keyword">soname</span> <span class="token string">'lib_mysqludf_json.so'</span><span class="token punctuation">;</span>
<span class="token keyword">create</span> <span class="token keyword">function</span> json_members <span class="token keyword">returns</span> string <span class="token keyword">soname</span> <span class="token string">'lib_mysqludf_json.so'</span><span class="token punctuation">;</span>
<span class="token keyword">create</span> <span class="token keyword">function</span> json_object <span class="token keyword">returns</span> string <span class="token keyword">soname</span> <span class="token string">'lib_mysqludf_json.so'</span><span class="token punctuation">;</span>
<span class="token keyword">create</span> <span class="token keyword">function</span> json_values <span class="token keyword">returns</span> string <span class="token keyword">soname</span> <span class="token string">'lib_mysqludf_json.so'</span><span class="token punctuation">;</span>
</code></pre></div><h3 id="删除-2"><a href="#删除-2" class="header-anchor">#</a> 删除</h3> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">DROP</span> <span class="token keyword">FUNCTION</span> <span class="token keyword">IF</span> <span class="token keyword">EXISTS</span> lib_mysqludf_json_info<span class="token punctuation">;</span>
<span class="token keyword">DROP</span> <span class="token keyword">FUNCTION</span> <span class="token keyword">IF</span> <span class="token keyword">EXISTS</span> json_array<span class="token punctuation">;</span>
<span class="token keyword">DROP</span> <span class="token keyword">FUNCTION</span> <span class="token keyword">IF</span> <span class="token keyword">EXISTS</span> json_members<span class="token punctuation">;</span>
<span class="token keyword">DROP</span> <span class="token keyword">FUNCTION</span> <span class="token keyword">IF</span> <span class="token keyword">EXISTS</span> json_object<span class="token punctuation">;</span>
<span class="token keyword">DROP</span> <span class="token keyword">FUNCTION</span> <span class="token keyword">IF</span> <span class="token keyword">EXISTS</span> json_values<span class="token punctuation">;</span>
</code></pre></div><h3 id="验证是否安装成功-2"><a href="#验证是否安装成功-2" class="header-anchor">#</a> 验证是否安装成功</h3> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> mysql<span class="token punctuation">.</span>func<span class="token punctuation">;</span> 
</code></pre></div><h3 id="实例-2"><a href="#实例-2" class="header-anchor">#</a> 实例</h3> <ul><li>返回json对象</li></ul> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">select</span> json_object<span class="token punctuation">(</span>login_name <span class="token keyword">as</span> <span class="token keyword">user</span><span class="token punctuation">,</span>login_password <span class="token keyword">as</span> pwd<span class="token punctuation">)</span> <span class="token keyword">as</span> <span class="token keyword">user</span> <span class="token keyword">from</span> t_sys_loginperson<span class="token punctuation">;</span>
</code></pre></div><ul><li>返回json数组</li></ul> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">select</span> json_array<span class="token punctuation">(</span>login_name<span class="token punctuation">,</span>login_password<span class="token punctuation">)</span> <span class="token keyword">as</span> <span class="token keyword">user</span> <span class="token keyword">from</span> t_sys_loginperson<span class="token punctuation">;</span>
</code></pre></div><h3 id="创建触发器"><a href="#创建触发器" class="header-anchor">#</a> 创建触发器</h3> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token comment">/* INSERT插入操作的触发器 */</span>  
<span class="token comment">/*开头将结束符号定义为|*/</span>
<span class="token keyword">DELIMITER</span> <span class="token operator">|</span>  
<span class="token keyword">DROP</span> <span class="token keyword">TRIGGER</span> <span class="token keyword">IF</span> <span class="token keyword">EXISTS</span> mytable_insert<span class="token punctuation">;</span>  
<span class="token keyword">CREATE</span> <span class="token keyword">TRIGGER</span> mytable_insert  
<span class="token keyword">AFTER</span> <span class="token keyword">INSERT</span> <span class="token keyword">ON</span> mytable  
<span class="token keyword">FOR EACH ROW</span> <span class="token keyword">BEGIN</span>  
    <span class="token keyword">SET</span> <span class="token variable">@tt_json</span> <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> json_object<span class="token punctuation">(</span>id<span class="token punctuation">,</span>addtime<span class="token punctuation">,</span>title<span class="token punctuation">)</span> <span class="token keyword">FROM</span> mytable <span class="token keyword">WHERE</span> id <span class="token operator">=</span> NEW<span class="token punctuation">.</span>id <span class="token keyword">LIMIT</span> <span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span>  
    <span class="token keyword">SET</span> <span class="token variable">@tt_resu</span> <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> http_put<span class="token punctuation">(</span>CONCAT<span class="token punctuation">(</span><span class="token string">'http://192.168.8.34:1978/'</span><span class="token punctuation">,</span> NEW<span class="token punctuation">.</span>id<span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token variable">@tt_json</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>  
<span class="token comment">/*使用|结束*/</span>
<span class="token keyword">END</span> <span class="token operator">|</span>  
<span class="token comment">/*最后使用DELIMITER ; 将结束符号还原*/</span>
<span class="token keyword">DELIMITER</span> <span class="token punctuation">;</span>  
</code></pre></div><div class="language-sql extra-class"><pre class="language-sql"><code><span class="token comment">/* UPDATE更新操作的触发器 */</span>  
<span class="token keyword">DELIMITER</span> <span class="token operator">|</span>  
<span class="token keyword">DROP</span> <span class="token keyword">TRIGGER</span> <span class="token keyword">IF</span> <span class="token keyword">EXISTS</span> mytable_update<span class="token punctuation">;</span>  
<span class="token keyword">CREATE</span> <span class="token keyword">TRIGGER</span> mytable_update  
<span class="token keyword">AFTER</span> <span class="token keyword">UPDATE</span> <span class="token keyword">ON</span> mytable  
<span class="token keyword">FOR EACH ROW</span> <span class="token keyword">BEGIN</span>
	<span class="token comment">/*判断表中字段新数据与旧数据变化*/</span>
	<span class="token keyword">if</span> new<span class="token punctuation">.</span><span class="token keyword">status</span><span class="token operator">&lt;&gt;</span>old<span class="token punctuation">.</span><span class="token keyword">status</span> <span class="token keyword">then</span>
    <span class="token keyword">SET</span> <span class="token variable">@tt_json</span> <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> json_object<span class="token punctuation">(</span>id<span class="token punctuation">,</span>addtime<span class="token punctuation">,</span>title<span class="token punctuation">)</span> <span class="token keyword">FROM</span> mytable <span class="token keyword">WHERE</span> id <span class="token operator">=</span> OLD<span class="token punctuation">.</span>id <span class="token keyword">LIMIT</span> <span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span>  
    <span class="token keyword">SET</span> <span class="token variable">@tt_resu</span> <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> http_get<span class="token punctuation">(</span>CONCAT<span class="token punctuation">(</span><span class="token string">'http://192.168.8.34:1978/'</span><span class="token punctuation">,</span> OLD<span class="token punctuation">.</span>id<span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token variable">@tt_json</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>  
	<span class="token comment">/*结束判断*/</span>
	<span class="token keyword">end</span> <span class="token keyword">if</span><span class="token punctuation">;</span>
<span class="token keyword">END</span> <span class="token operator">|</span>  
<span class="token keyword">DELIMITER</span> <span class="token punctuation">;</span>  
</code></pre></div><div class="language-sql extra-class"><pre class="language-sql"><code><span class="token comment">/* DELETE删除操作的触发器 */</span>  
<span class="token keyword">DELIMITER</span> <span class="token operator">|</span>  
<span class="token keyword">DROP</span> <span class="token keyword">TRIGGER</span> <span class="token keyword">IF</span> <span class="token keyword">EXISTS</span> mytable_delete<span class="token punctuation">;</span>  
<span class="token keyword">CREATE</span> <span class="token keyword">TRIGGER</span> mytable_delete  
<span class="token keyword">AFTER</span> <span class="token keyword">DELETE</span> <span class="token keyword">ON</span> mytable  
<span class="token keyword">FOR EACH ROW</span> <span class="token keyword">BEGIN</span>  
    <span class="token keyword">SET</span> <span class="token variable">@tt_resu</span> <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> http_delete<span class="token punctuation">(</span>CONCAT<span class="token punctuation">(</span><span class="token string">'http://192.168.8.34:1978/'</span><span class="token punctuation">,</span> OLD<span class="token punctuation">.</span>id<span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>  
<span class="token keyword">END</span> <span class="token operator">|</span>  
<span class="token keyword">DELIMITER</span> <span class="token punctuation">;</span>  
</code></pre></div></div> <footer class="page-edit"><div class="edit-link"><a href="https://github.com/woytu/notes-vuepress/edit/master//SQL/mysql-udf安装.md" target="_blank" rel="noopener noreferrer">在 GitHub 上编辑此页</a> <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></div> <div class="last-updated"><span class="prefix">上次更新:</span> <span class="time">2020-04-21 19:24:22</span></div></footer> <div class="page-nav"><p class="inner"><span class="prev">
      ←
      <a href="/SQL/MySQL笔记.html" class="prev">
        MySQL笔记
      </a></span> <!----></p></div> </main></div><div class="global-ui"><!----></div></div>
    <script src="/assets/js/app.269009d6.js" defer></script><script src="/assets/js/3.974b9720.js" defer></script><script src="/assets/js/55.01c4b876.js" defer></script>
  </body>
</html>
